﻿namespace HabraReader.Classes
{
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.Common;
    using System.Data.SQLite;
    using System.IO;
    using System.Text;

    public class Database
    {
        private readonly string DatabaseName;
        private StringDictionary[] Articles;
        private StringDictionary[] Blogs;

        public Database(string database)
        {
            this.DatabaseName = database;
        }

        public void AddArticle(string blogName, string articleName, string articleText)
        {
            int blogId = 0;
            SQLiteDataReader reader;
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                reader = this.GetReader(connection, @"SELECT * FROM [blog] WHERE [blog_name] = '" + blogName + "';");
                if (!reader.HasRows)
                {
                    this.AddBlog(blogName);
                    reader = this.GetReader(
                            connection, @"SELECT [blog_id] FROM [blog] WHERE [blog_name] = '" + blogName + "';");
                    while (reader.Read()) blogId = reader.GetInt32(0);
                }
                using (var command = new SQLiteCommand(connection))
                {
                    ExecuteNonQuery(command, @"INSERT INTO [article] ([article_name], [blog_id], [article_text])
                            VALUES ('" + articleName + "'," + blogId + ",'" + articleText + "');");
                }
                connection.Close();
            }
        }

        public void AddBlog(string blogName)
        {
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                connection.ConnectionString = "Data Source = " + this.DatabaseName;
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    ExecuteNonQuery(command, @"INSERT INTO [blog] ([blog_name]) VALUES ('" + blogName + "');");
                }
                connection.Close();
            }
        }

        public void Connect()
        {
            var databaseFile = new FileInfo(this.DatabaseName);
            if (!databaseFile.Exists || databaseFile.Length == 0)
            {
                SQLiteConnection.CreateFile(this.DatabaseName);
                SQLiteFactory factory = GetFactory();
                using (var connection = (SQLiteConnection)factory.CreateConnection())
                {
                    connection.ConnectionString = "Data Source = " + this.DatabaseName;
                    connection.Open();
                    using (var command = new SQLiteCommand(connection))
                    {
                        ExecuteNonQuery(command, "pragma auto_vacuum = 1");
                        ExecuteNonQuery(
                                command,
                                @"CREATE TABLE [blog] (
                                [blog_id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                [blog_name] char(255) NOT NULL
                                );");
                        ExecuteNonQuery(
                                command,
                                @"CREATE TABLE [article] (
                                [article_id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                [article_name] char(255) NOT NULL,
                                [blog_id] integer NOT NULL,
                                [article_text] text NOT NULL
                                );");
                        ExecuteNonQuery(
                                command,
                                @"CREATE TABLE [images] (
                                [image_id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                [article_id] integer NOT NULL,
                                [image_name] text NOT NULL,
                                [image] blob NOT NULL
                                );");
                    }
                    connection.Close();
                }
            }
        }

        public void GetArticle(string articleName)
        {
            int id = 0;
            string text = "";
            SQLiteDataReader reader;
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                reader = this.GetReader(connection, @"SELECT [article_id], [article_text] FROM [article] WHERE [article_name] = '" 
                        + articleName + "';");
                while (reader.Read())
                {
                    id = int.Parse(reader.GetValue(0).ToString());
                    text = reader.GetValue(1).ToString();
                }
                connection.Close();

                if (Directory.Exists("temp")) Directory.Delete("temp", true);
                Directory.CreateDirectory("temp");
                File.Copy(@"include\all.css", @"temp\all.css");
                var writer = new StreamWriter(@"temp\temp.html", true, Encoding.UTF8);
                writer.WriteLine(
                        "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">");
                writer.WriteLine("<html xml:lang=\"ru\" xmlns=\"http://www.w3.org/1999/xhtml\">");
                writer.WriteLine("<head>");
                writer.WriteLine("<meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\" />");
                writer.WriteLine("<link href=\"all.css\" rel=\"stylesheet\" media=\"all\" />");
                writer.WriteLine("</head>");
                writer.WriteLine(
                        "<body id=\"main-page\" lang=\"ru\" class=\"blogs\"><div id=\"wrapper\"><div id=\"main-content\">");
                writer.WriteLine(text);
                writer.WriteLine("</div></div></body></html>");
                writer.Close();

                reader = this.GetReader(connection, @"SELECT [image_name], [image] FROM [images] WHERE [article_id] = " + id + ";");

                while (reader.Read())
                {
                    string filename = reader.GetValue(0).ToString();
                    var fileStream = new FileStream(@"temp\" + filename, FileMode.Create, FileAccess.Write);
                    var streamWriter = new StreamWriter(fileStream);
                    var binaryWriter = new BinaryWriter(fileStream);
                    const int bufferSize = 1024 * 1024 * 9;
                    var buffer = new byte[bufferSize];
                    long startIndex = 0;
                    long value = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
                    while (value == bufferSize)
                    {
                        binaryWriter.Write(buffer);
                        binaryWriter.Flush();
                        startIndex += bufferSize;
                        value = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
                    }
                    binaryWriter.Write(buffer, 0, (int)value);
                    binaryWriter.Flush();
                    streamWriter.Write(buffer);
                    streamWriter.Flush();
                    fileStream.Close();
                }
                connection.Close();
            }
        }

        public StringDictionary[] GetArticles(int blogId)
        {
            int i = 0;
            SQLiteDataReader reader;
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                reader = this.GetReader(connection,
                        @"SELECT [article_id],[article_name] FROM [article] WHERE [blog_id] = " + blogId + ";");
                int articlesCount = this.GetArticlesCount(blogId);
                this.Articles = new StringDictionary[articlesCount];
                while (reader.Read())
                {
                    this.Articles[i] = new StringDictionary();
                    this.Articles[i]["article_id"] = reader.GetValue(0).ToString();
                    this.Articles[i]["article_name"] = reader.GetValue(1).ToString();
                    i++;
                }
                connection.Close();
            }
            return this.Articles;
        }

        private int GetArticlesCount(int blogId)
        {
            return this.GetCount(@"SELECT [article_id] FROM [article] WHERE [blog_id] = " + blogId + ";");
        }

        public StringDictionary[] GetBlogs()
        {
            int i = 0;
            SQLiteDataReader reader;
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                reader = this.GetReader(connection, @"SELECT * FROM [blog];");
                int blogsCount = this.GetBlogsCount();
                this.Blogs = new StringDictionary[blogsCount];
                while (reader.Read())
                {
                    this.Blogs[i] = new StringDictionary();
                    this.Blogs[i]["blog_id"] = reader.GetValue(0).ToString();
                    this.Blogs[i]["blog_name"] = reader.GetValue(1).ToString();
                    i++;
                }
                connection.Close();
            }
            return this.Blogs;
        }

        public int GetBlogsCount()
        {
            return this.GetCount(@"SELECT [blog_id] FROM [blog];");
        }

        public void InsertImage(int articleId, string imageFilename)
        {
            string filename = imageFilename;
            byte[] blob;
            using (var fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                blob = new byte[fileStream.Length];
                fileStream.Read(blob, 0, blob.Length);
            }
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                connection.ConnectionString = "Data Source = " + this.DatabaseName;
                connection.Open();

                using (var command = new SQLiteCommand(connection))
                {
                    var dataAdapter = new SQLiteDataAdapter();
                    command.CommandText = "INSERT INTO [images] ([article_id], [image_name], [image]) VALUES (" + articleId
                                 + ", '" + filename + "', @image)";
                    command.CommandType = CommandType.Text;
                    dataAdapter.InsertCommand = command;
                    command.Parameters.Add("@image", DbType.Binary, blob.Length).Value = blob;
                    command.ExecuteNonQuery();
                }
                connection.Close();
            }
        }

        private static void ExecuteNonQuery(SQLiteCommand command, string query)
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();
        }

        private int GetCount(string query)
        {
            int i = 0;
            SQLiteFactory factory = GetFactory();
            using (var connection = (SQLiteConnection)factory.CreateConnection())
            {
                SQLiteDataReader reader = this.GetReader(connection, query);
                while (reader.Read()) i++;
                connection.Close();
            }
            return i;
        }

        private SQLiteDataReader GetReader(SQLiteConnection connection, string query)
        {
            SQLiteDataReader reader;
            connection.ConnectionString = "Data Source = " + this.DatabaseName;
            connection.Open();
            using (var command = new SQLiteCommand(connection))
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                reader = command.ExecuteReader();
            }
            return reader;
        }

        private static SQLiteFactory GetFactory()
        {
            return (SQLiteFactory)DbProviderFactories.GetFactory("System.Data.SQLite");
        }

        public int GetAllArticlesCount()
        {
            return this.GetCount(@"SELECT [article_id] FROM [article];");
        }
    }
}